//
//  XYSqlite3.m
//  项目二三方库使用
//
//  Created by rimi on 15/12/9.
//  Copyright © 2015年 xiaoyi. All rights reserved.
//

#import "XYSqlite3.h"
static FMDatabase *database = nil;
@interface XYSqlite3 ()

@end

@implementation XYSqlite3

/*数据库*/
/*获取数据库并打开 ok*/
- (void)openDataBase {
    if (!database) {
        NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)objectAtIndex:0];
        NSString *dbPath = [docPath stringByAppendingPathComponent:@"iFeller.db"];
        
        //获取数据库并打开
        database = [FMDatabase databaseWithPath:dbPath];
    }
    if (![database open]) {
        NSLog(@"open failed");
        return;
    }
    
    NSLog(@"沙盒路径：%@",NSHomeDirectory());
}
 
/*关闭数据库*/
- (void)closeTheDataBase {
    [database close];
}

/*重新打开数据库*/
- (void)reloadTheDataBase {
    [database open];
}

/*创建一个表*/
- (void)creatTheTable:(NSString *)tableName {
    if ([database open]) {
        if (![self isTableOK:tableName]) {
            //创建表
            [database executeUpdate:[NSString stringWithFormat:@"create table %@ (name text, time text, repeat text, tags text, notes text, date text , finishDate text)", tableName]];
        }
    }
    [database close];
}

/*插入数据 ok*/
- (void)insertDataSourceToDBwithArray:(NSArray *)array
                            tableName:(NSString *)tableName {
    if ([database open]) {
        BOOL insert = [database executeUpdate:[NSString stringWithFormat: @"INSERT INTO '%@' VALUES ('%@', '%@', '%@', '%@', '%@', '%@' , '%@')", tableName, array[0], array[1], array[2], array[3], array[4], array[5] , array[6]]];
        if (insert) {
            NSLog(@"插入数据成功");
        }
    }
    [database close];
}

/*更新数据*//*修改数据 ok*/
- (void)updateDataBaseWithTableName:(NSString *)tableName
                         changeItem:(NSString *)changeItem //改变的项
                      changeContent:(NSString *)changeContent
                         changeName:(NSString *)changeName {
    if ([database open]) {
        BOOL update = [database executeUpdate:[NSString stringWithFormat:@"UPDATE '%@' SET '%@' = '%@' WHERE name = '%@'", tableName, changeItem, changeContent, changeName]];
        
        if (update) {
            NSLog(@"更新数据成功");
        }
    }
    [database close];
}

/*该表是否存在*/
- (BOOL)isTableOK:(NSString *)tableName {
    if ([database open]) {
        FMResultSet *rs = [database executeQuery:@"select count(*) as 'count' from sqlite_master where type ='table' and name = ?", tableName];
        while ([rs next]) {
            // just print out what we've got in a number of formats.
            NSInteger count = [rs intForColumn:@"count"];
            
            if (0 == count) {
                return NO;
            } else {
                return YES;
            }
        }
        return NO;
    }
    [database close];
    return NO;
}

/*查询整个表 ok*/
- (NSMutableArray *)searchTheDataBaseWithtableName:(NSString *)tableName {
    NSMutableArray *dataArray = [NSMutableArray array];
    if ([database open]) {
        FMResultSet *resultSet = [database executeQuery:[NSString stringWithFormat: @"SELECT * FROM %@", tableName]];
        while ([resultSet next]) {
            NSString *name = [resultSet stringForColumn:@"name"];
            NSString *time = [resultSet stringForColumn:@"time"];
            NSString *repeat = [resultSet stringForColumn:@"repeat"];
            NSString *tags = [resultSet stringForColumn:@"tags"];
            NSString *notes = [resultSet stringForColumn:@"notes"];
            NSString *date = [resultSet stringForColumn:@"date"];
            NSString *finishDate = [resultSet stringForColumn:@"finishDate"];
//            NSLog(@"Name:%@,time:%@,repeat:%@,tags:%@,notes:%@,date:%@,finishDate:%@", name, time,repeat, tags, notes, date ,finishDate);
            NSArray *array = @[name , time , repeat , tags , notes , date , finishDate];
            [dataArray addObject:array];
        }
    }
    [database close];
    return dataArray;
}

/*条件查询 ok*/
/*通过事件名称*/
- (NSMutableArray *)conditionSearchTheDataBaseWithTableName:(NSString *)tableName
                                     searchName:(NSString *)searchName {
    NSMutableArray *dataArray = [NSMutableArray array];
    FMResultSet *resultSet = [database executeQuery:[NSString stringWithFormat: @"SELECT * FROM '%@' WHERE name = '%@'", tableName, searchName]];
    if ([database open]) {
        while ([resultSet next]) {
            NSString *name = [resultSet stringForColumn:@"name"];
            NSString *time = [resultSet stringForColumn:@"time"];
            NSString *repeat = [resultSet stringForColumn:@"repeat"];
            NSString *tags = [resultSet stringForColumn:@"tags"];
            NSString *notes = [resultSet stringForColumn:@"notes"];
            NSString *date = [resultSet stringForColumn:@"date"];
            NSString *finishDate = [resultSet stringForColumn:@"finishDate"];
//            NSLog(@"Name:%@,time:%@,repeat:%@,tags:%@,notes:%@,date:%@,finishDate:%@", name, time, repeat, tags, notes, date,finishDate);
            NSArray *array = @[name , time , repeat , tags , notes , date , finishDate];
            [dataArray addObject:array];
        }
    }
    [database close];
    return dataArray;
}

/*通过完成日期查询*/
- (NSMutableArray *)conditionSearchTheDataBaseWithTableName:(NSString *)tableName
                                           searchFinishDate:(NSString *)searchFinishDate {
    NSMutableArray *dataArray = [NSMutableArray array];
    FMResultSet *resultSet = [database executeQuery:[NSString stringWithFormat: @"SELECT * FROM '%@' WHERE finishDate = '%@'", tableName, searchFinishDate]];
    if ([database open]) {
        while ([resultSet next]) {
            NSString *name = [resultSet stringForColumn:@"name"];
            NSString *time = [resultSet stringForColumn:@"time"];
            NSString *repeat = [resultSet stringForColumn:@"repeat"];
            NSString *tags = [resultSet stringForColumn:@"tags"];
            NSString *notes = [resultSet stringForColumn:@"notes"];
            NSString *date = [resultSet stringForColumn:@"date"];
            NSString *finishDate = [resultSet stringForColumn:@"finishDate"];
            //            NSLog(@"Name:%@,time:%@,repeat:%@,tags:%@,notes:%@,date:%@,finishDate:%@", name, time, repeat, tags, notes, date,finishDate);
            NSArray *array = @[name , time , repeat , tags , notes , date , finishDate];
            [dataArray addObject:array];
        }
    }
    [database close];
    return dataArray;
}

/*通过日期查询*/
- (NSMutableArray *)conditionSearchTheDataBaseWithTableName:(NSString *)tableName
                                     searchDate:(NSString *)searchDate {
    NSMutableArray *dataArray = [NSMutableArray array];
    FMResultSet *resultSet = [database executeQuery:[NSString stringWithFormat: @"SELECT * FROM '%@' WHERE date = '%@'", tableName, searchDate]];
    if ([database open]) {
        while ([resultSet next]) {
            NSString *name = [resultSet stringForColumn:@"name"];
            NSString *time = [resultSet stringForColumn:@"time"];
            NSString *repeat = [resultSet stringForColumn:@"repeat"];
            NSString *tags = [resultSet stringForColumn:@"tags"];
            NSString *notes = [resultSet stringForColumn:@"notes"];
            NSString *date = [resultSet stringForColumn:@"date"];
            NSString *finishDate = [resultSet stringForColumn:@"finishDate"];
//            NSLog(@"Name:%@,time:%@,repeat:%@,tags:%@,notes:%@,date:%@,finishDate:%@", name, time, repeat, tags, notes, date ,finishDate);
            NSArray *array = @[name , time , repeat , tags , notes , date , finishDate];
            [dataArray addObject:array];
        }
    }
    [database class];
    return dataArray;
}

/*删除任务 ok*/
- (void)deleteTheDataBaseWithTableName:(NSString *)tableName
                            deleteName:(NSString *)deleteName {
    if ([database open]) {
        BOOL delete = [database executeUpdate:[NSString stringWithFormat:@"DELETE FROM '%@' WHERE name = '%@'", tableName, deleteName]];
        if (delete) {
            NSLog(@"删除成功");
        }
    }
    [database close];
}

@end
